package com.jackrain.nea.web.controller;

import com.alibaba.dubbo.config.annotation.Reference;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.*;
import com.jackrain.nea.config.PropertiesConf;
import com.jackrain.nea.core.schema.*;
import com.jackrain.nea.core.schema.Table;
import com.jackrain.nea.exception.NDSException;
import com.jackrain.nea.ext.oss.OssHelper;
import com.jackrain.nea.ext.oss.OssObject;
import com.jackrain.nea.ext.permission.Permissions;
import com.jackrain.nea.util.*;
import com.jackrain.nea.util.DateUtil;
import com.jackrain.nea.utility.ExceptionUtil;
import com.jackrain.nea.web.BigPipeCmd;
import com.jackrain.nea.web.ImportCmd;
import com.jackrain.nea.web.SearchCmd;
import com.jackrain.nea.web.common.Handle;
import com.jackrain.nea.web.common.WebIntercept;
import com.jackrain.nea.web.face.User;
import com.jackrain.nea.web.query.DefaultWebEvent;
import com.jackrain.nea.web.query.QuerySession;
import com.jackrain.nea.web.query.QueryUtils;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.FastDateFormat;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletRequest;
import java.io.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author lzm
 * @date 2017-11-4
 */
@Slf4j
@RestController
@Api(value = "ImportController", description = "数据导入")
public class ImportController {
    private static final int EXCEL_MAX_ROW_COUNT = 65535;    //excel最大行数
    private static FormulaEvaluator evaluator;

    @Autowired
    private Handle handle;

    @Reference(version = "1", group = "ad", timeout = 60 * 60 * 1000)
    private BigPipeCmd batchCmd1;
    @Reference(version = "2", group = "cp", timeout = 60 * 60 * 1000)
    private BigPipeCmd batchCmd2;
    @Reference(version = "3", group = "ps", timeout = 60 * 60 * 1000)
    private BigPipeCmd batchCmd3;
    @Reference(version = "4", group = "vp", timeout = 60 * 60 * 1000)
    private BigPipeCmd batchCmd4;

    @Reference(version = "1", group = "ad")
    private ImportCmd importCmd1;
    @Reference(version = "2", group = "cp")
    private ImportCmd importCmd2;
    @Reference(version = "3", group = "ps")
    private ImportCmd importCmd3;
    @Reference(version = "4", group = "vp")
    private ImportCmd importCmd4;
    private String importServiceName = "com.jackrain.nea.web.ImportCmd";

    @Reference(version = "1", group = "ad")
    private SearchCmd searchCmd1;
    @Reference(version = "2", group = "cp")
    private SearchCmd searchCmd2;
    @Reference(version = "3", group = "ps")
    private SearchCmd searchCmd3;
    @Reference(version = "4", group = "vp")
    private SearchCmd searchCmd4;
    private String searchServiceName = "com.jackrain.nea.web.SearchCmd";

    @Autowired
    private PropertiesConf pconf;

    public static OssHelper ossHelper;
//    public static String endpoint = "http://oss-cn-hangzhou.aliyuncs.com";

    @PostConstruct
    public void afterInit() {
        if (ossHelper == null) {
            String accessKeyId = pconf.getProperty("ons.accessKey");
            String accessKeySecret = pconf.getProperty("ons.secretKey");
            String bucketName = pconf.getProperty("oss.bucketName");
            String endpoint = pconf.getProperty("oss.endpoint");
            ossHelper = new OssHelper(accessKeyId, accessKeySecret, endpoint, bucketName);
        }
    }

    @ApiOperation(value = "导入", notes = "导入功能")
    @RequestMapping(path = "/p/cs/import", method = RequestMethod.POST)
    public JSONObject importRecords(HttpServletRequest request
            , @RequestParam(value = "file", required = true) MultipartFile file
            , @RequestParam(value = "table", required = true) String tableName
            , @RequestParam(value = "mainTable", required = false) String mainTableName
            , @RequestParam(value = "mainId", required = false) Long mainId) throws IOException {
        User user = (User) ShiroSessionUtil.getAttribute("user");
//        User user = Security4Utils.getUser("root");
        QuerySession session = QueryUtils.createQuerySession(user);
        TableManager tm = session.getTableManager();
        ValueHolder result = new ValueHolder();

        if (!WebIntercept.checkKey(request)) {
            result.put("code", -1);
            result.put("message", "请勿连续点击！");
            return result.toJSONObject();
        }

        Table table = tm.getTable(tableName);
        int rowCount = 0;
        int failedCount = 0;
        SortedMap<Integer, JSONObject> errors = new TreeMap<>();
        //1. 入口参数的校验
        try {
            HSSFWorkbook workbook = getExcelWorkbook(file, tableName, table);

            //2. 标题列的处理
            Sheet sheet = workbook.getSheet(table.getDescription());
            if (sheet == null) {
                throw new NDSException(String.format("上传的excel不存在名为%s的工作表!", table.getDescription()));
            }
            HashMap<Column, Integer> columnMap = processTitleRow(session, user, sheet, table);

            //3. 检验是主表导入还是明细导入
            Long mainRecordId = -1L;
            boolean isImportDetails = false;
            if (mainTableName == null || mainTableName.isEmpty()) {
                mainTableName = table.getName();
            } else {
                if (mainId == null || mainId <= 0) {
                    throw new NDSException(String.format("明细表导入时需传入主表id!"));
                }
                mainRecordId = mainId;
                isImportDetails = true;
            }

            Table mainTable = tm.getTable(mainTableName);
            if (mainTable == null) {
                throw new NDSException(String.format("主表%s不存在", mainTableName));
            }

            //4. 数据的处理，构造出所有的提交到hsf数据
            HashMap<Integer, JSONObject> rows = processRows(errors, session, sheet, table, columnMap);

            //5. 批量服务的调用
            rowCount = rows.size() + errors.size();
            failedCount = errors.size();
            JSONObject batchRequests = new JSONObject();
            batchRequests.put("table", mainTableName);
            JSONObject datas = new JSONObject();
            batchRequests.put("data", datas);
            for (Integer rowIndex : rows.keySet()) {
                JSONObject importRecord = rows.get(rowIndex);
                if (isImportDetails && (!importRecord.containsKey("ID") || importRecord.getLong("ID") == null)) {
                    importRecord.put("ID", -1L);
                }

                JSONObject jor = new JSONObject();
                jor.put("objid", mainRecordId);  //TODO：需要考虑兼容update操作么？
                JSONObject fixcolumn = new JSONObject();
                if (isImportDetails) {
                    JSONArray temp = new JSONArray();
                    temp.add(importRecord);
                    fixcolumn.put(table.getName(), temp);
                } else {
                    fixcolumn.put(table.getName(), importRecord);
                }
                jor.put("fixcolumn", fixcolumn);
                TriggerHolder.VersionedTrigger trigger = table.getTrigger("TRI");
                if (trigger != null) {
                    jor.put("aspecttrigger", trigger.toJSONObject());
                }
                datas.put(rowIndex.toString(), jor);
            }
            if (rowCount <= 100) {
                log.debug(String.format("import.invokeBigPipe, table: %s, mainTable:%s, mainId:%d 一共%d条, 请求参数为%s", tableName, mainTableName, mainRecordId, rowCount, batchRequests));
            } else {
                log.debug(String.format("import.invokeBigPipe, table: %s, mainTable:%s, mainId:%d 一共%d条", tableName, mainTableName, mainRecordId, rowCount));
            }
            if (!datas.isEmpty()) {
                DefaultWebEvent event = new DefaultWebEvent(isImportDetails ? "doSave" : "doAdd", Maps.newHashMap());
                String center = mainTable.getCategory().getSubSystem().getCenter();

                ValueHolder valueHolder = handle.batchProcess(user, batchRequests, center, event, isImportDetails ? "save" : "add");
                JSONObject saveResult = valueHolder.toJSONObject();
                if (rowCount <= 100) {
                    log.debug(String.format("import.invokeBigPipe.END, table: %s, mainTable:%s, mainId:%d 一共%d条, 返回值是%s", tableName, mainTableName, mainRecordId, rowCount, saveResult));
                } else {
                    log.debug(String.format("import.invokeBigPipe.END, table: %s, mainTable:%s, mainId:%d 一共%d条", tableName, mainTableName, mainRecordId, rowCount));
                }

                int code = Tools.getInt(String.valueOf(saveResult.get("code")), 0);
                if (code < 0) {
                    //只要返回值小于0，则认定为整批失败
                    failedCount = rowCount;
                    errors.clear();
                    JSONObject currentError = new JSONObject();
                    currentError.put("rowIndex", -1);
                    currentError.put("message", saveResult.getString("message"));
                    errors.put(-1, currentError);
                } else {
                    //说明批次调用本身没问题，有正常返回
                    JSONObject subResults = saveResult.getJSONObject("results");
                    for (String rowIndex : subResults.keySet()) {
                        JSONObject subResult = subResults.getJSONObject(rowIndex);
                        int subCode = Tools.getInt(String.valueOf(subResult.get("code")), 0);
                        if (subCode < 0) {
                            JSONObject currentError = new JSONObject();
                            currentError.put("rowIndex", rowIndex);
                            String message = subResult.getString("message");
                            JSONArray tempErrorData = subResult.getJSONArray("data");
                            if (tempErrorData != null && tempErrorData.size() > 0) {
                                String tempMessage = tempErrorData.getJSONObject(0).getString("message");
                                if (tempMessage != null && !tempMessage.isEmpty()) {
                                    message = tempMessage;
                                }
                            }
                            currentError.put("message", message);
                            currentError.put("isInner", true);
                            errors.put(Tools.getInt(rowIndex, -1), currentError);
                            failedCount++;
                        }
                    }
                }
            }
        } catch (NDSException e) {
            result.put("code", -1);
            result.put("message", e.getMessage());
            return result.toJSONObject();
        } catch (Exception e) {
            StringWriter sw = new StringWriter();
            PrintWriter pw = new PrintWriter(sw);
            e.printStackTrace(pw);
            result.put("code", -2);
            result.put("message", sw.toString().replace("\n", "<br/>"));
            return result.toJSONObject();
        }

        if (failedCount > 0) {
            result.put("code", -1);
            JSONArray errorArray = new JSONArray();
            for (Integer rowIndex : errors.keySet()) {
                errorArray.add(errors.get(rowIndex));
            }
            result.put("data", errorArray);
            result.put("path", generateResultExcel(user, table.getDescription() + "-错误信息", errors));
            result.put("message", "插入成功记录数：" + (rowCount - failedCount) + "，插入失败记录数：" + failedCount);
        } else {
            result.put("code", 0);
            result.put("message", "插入成功记录数：" + rowCount);
        }

        return result.toJSONObject();
    }

    /**
     * 导入进度查询
     *
     * @returnq
     */
    @RequestMapping(path = "/p/cs/importProgress", method = RequestMethod.POST)
    @ApiOperation(value = "文件上传进度查询", httpMethod = "POST")
    public JSONObject importProgress(@RequestParam(value = "importId") String importId) {
        ValueHolder vh = new ValueHolder();

        try {
            int progress = (int) ossHelper.uploadProgress(importId);
            if (progress < 0) {
                vh.put("code", -1);
                vh.put("message", "not found");
            } else {
                vh.put("code", 0);
                vh.put("progress", progress);
                if(progress >= 100){
                    OssObject metaDataInfo = ossHelper.getInfo(importId);
                    vh.put("result", metaDataInfo.getProp("result"));
                }
            }
        } catch (Exception e) {
            vh.put("code", -1);
            vh.put("message", "Fail");
        }
        return vh.toJSONObject();
    }

//    public static void main(String[] args) throws FileNotFoundException {
//        JSONObject obj1 = new JSONObject(); obj1.put("message", "上级商品属性: 344444 不存在！\n");
//        JSONObject obj2 = new JSONObject(); obj2.put("message", "Exception From Host:[172.16.8.13]输入的数据已存在：编码\n");
//        JSONObject obj3 = new JSONObject(); obj3.put("message", "Exception From Host:[172.16.8.13]输入的数据已存在：编码\n");
//        JSONObject obj4 = new JSONObject(); obj4.put("message", "上级商品属性: 109 不存在！\n");
//        JSONObject obj5 = new JSONObject(); obj5.put("message", "Exception From Host:[172.16.8.13]输入的数据已存在：编码\n");
//        JSONObject obj6 = new JSONObject(); obj6.put("message", "Exception From Host:[172.16.8.13]输入的数据已存在：编码\n");
//        SortedMap<Integer, JSONObject> errors = Maps.newTreeMap();
//        errors.put(1, obj1);
//        errors.put(3, obj2);
//        errors.put(4, obj3);
//        errors.put(5, obj4);
//        errors.put(6, obj5);
//        errors.put(7, obj6);
//
//        String path = generateResultExcel(null, "xxx", errors);
//        System.out.printf(path);
//    }
    private static String generateResultExcel(User user, String fileName, SortedMap<Integer, JSONObject> errors) throws FileNotFoundException {
        String randomId = UUID.randomUUID().toString().replace("-", "");
        String virtualBaseDir ="/import/" + user.getId() +"/Result/" + randomId +"/";

        String virtualPath = virtualBaseDir + fileName + ".xls";
        String physicalBaseDir = ResourceUtils.getURL("/home/admin/tmp/").getPath() + virtualBaseDir;
        String fullFileName = physicalBaseDir + fileName + ".xls";

        File svrDir = new File(physicalBaseDir);
        if (!svrDir.isDirectory()) {
            svrDir.mkdirs();
        }

        try {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet(fileName);

            sheet.createFreezePane(0, 1, 0, 1);

            CellStyle titleStyle = wb.createCellStyle();
            Font titleFont = wb.createFont();
            titleFont.setFontHeightInPoints((short) 10);
            titleFont.setFontName("宋体");
            titleFont.setColor(HSSFColor.DARK_BLUE.index);
            titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            titleStyle.setFont(titleFont);

            CellStyle style = wb.createCellStyle();
            Font font = wb.createFont();
            font.setFontHeightInPoints((short) 10);
            font.setFontName("宋体");
            style.setFont(font);
            //font.setColor(HSSFColor.DARK_BLUE.index);
            style.setWrapText(true);

            Row titleRow = sheet.createRow(0);
            createCell(titleStyle, titleRow, 0, "行号");
            createCell(titleStyle, titleRow, 1, "错误信息");

            int index = 1;
            for (Integer rowIndex: errors.keySet()){
                Row row = sheet.createRow(index);
                createCell(style, row, 0, rowIndex.toString());
                createCell(style, row, 1, errors.get(rowIndex).getString("message"));
                index++;
            }
            //sheet.autoSizeColumn(0);
            sheet.autoSizeColumn(1);

            index = 1;
            for (Integer rowIndex: errors.keySet()){
                Row row = sheet.getRow(index);
                if(row == null){
                    continue;
                }
                try {
                    calcAndSetRowHeigt(row, font, errors.get(rowIndex).getString("message"));
                }
                catch (Exception e){
                    log.debug("calcAndSetRowHeigt报错：", ExceptionUtil.getMessage(e));
                }
                index++;
            }
            FileOutputStream fileOut = new FileOutputStream(fullFileName);
            wb.write(fileOut);
            fileOut.close();
        }catch (Exception e){
            log.debug("generateResultExcel:" + ExceptionUtil.getMessage(e));
        }
        return virtualPath;
    }

    public static void calcAndSetRowHeigt(Row sourceRow, Font font, String message) {
            //行高
            double maxHeight = sourceRow.getHeight();
            Cell sourceCell = sourceRow.getCell(1);
            //单元格的内容
            String cellContent = message;
            if(null == cellContent || "".equals(cellContent)){
                return;
            }

            //单元格的宽高及单元格信息
            Map<String, Object> cellInfoMap = getCellInfo(sourceCell);
            Integer cellWidth = (Integer)cellInfoMap.get("width");
            Integer cellHeight = (Integer)cellInfoMap.get("height");
            if(cellHeight > maxHeight){
                maxHeight = cellHeight;
            }
            //字体的高度
            short fontHeight = font.getFontHeight();

            //cell内容字符串总宽度
            double cellContentWidth = cellContent.getBytes().length * 2 * 256;

            //字符串需要的行数 不做四舍五入之类的操作
            double stringNeedsRows =cellContentWidth / cellWidth;
            //小于一行补足一行
            if(stringNeedsRows < 1.0){
                stringNeedsRows = 1.0;
            }

            //需要的高度             (Math.floor(stringNeedsRows) - 1) * 40 为两行之间空白高度
            double stringNeedsHeight = (double)fontHeight * stringNeedsRows;
            //需要重设行高
            if(stringNeedsHeight > maxHeight){
                maxHeight = stringNeedsHeight;
                //超过原行高三倍 则为5倍 实际应用中可做参数配置
                if(maxHeight/cellHeight > 5){
                    maxHeight = 5 * cellHeight;
                }
                //最后取天花板防止高度不够
                maxHeight = Math.ceil(maxHeight);
                //重新设置行高 同时处理多行合并单元格的情况
//                Boolean isPartOfRowsRegion = (Boolean)cellInfoMap.get("isPartOfRowsRegion");
//                if(isPartOfRowsRegion){
//                    Integer firstRow = (Integer)cellInfoMap.get("firstRow");
//                    Integer lastRow = (Integer)cellInfoMap.get("lastRow");
//                    //平均每行需要增加的行高
//                    double addHeight = (maxHeight - cellHeight)/(lastRow - firstRow + 1);
//                    for (int i = firstRow; i <= lastRow; i++) {
//                        double rowsRegionHeight =sourceRow.getSheet().getRow(i).getHeight() + addHeight;
//                        sourceRow.getSheet().getRow(i).setHeight((short)rowsRegionHeight);
//                    }
//                }else{
                    sourceRow.setHeight((short)maxHeight);
//                }
            }
    }

    private static Map<String, Object> getCellInfo(Cell cell) {
        Sheet sheet = cell.getSheet();
        int rowIndex = cell.getRowIndex();
        int columnIndex = cell.getColumnIndex();

        boolean isPartOfRegion = false;
        int firstColumn = 0;
        int lastColumn = 0;
        int firstRow = 0;
        int lastRow = 0;
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            firstColumn = ca.getFirstColumn();
            lastColumn = ca.getLastColumn();
            firstRow = ca.getFirstRow();
            lastRow = ca.getLastRow();
            if (rowIndex >= firstRow && rowIndex <= lastRow) {
                if (columnIndex >= firstColumn && columnIndex <= lastColumn) {
                    isPartOfRegion = true;
                    break;
                }
            }
        }
        Map<String, Object> map = new HashMap<String, Object>();
        Integer width = 0;
        Integer height = 0;
        boolean isPartOfRowsRegion = false;
        if(isPartOfRegion){
            for (int i = firstColumn; i <= lastColumn; i++) {
                width += sheet.getColumnWidth(i);
            }
            for (int i = firstRow; i <= lastRow; i++) {
                height += sheet.getRow(i).getHeight();
            }
            if(lastRow > firstRow){
                isPartOfRowsRegion = true;
            }
        }else{
            width = sheet.getColumnWidth(columnIndex);
            height += cell.getRow().getHeight();
        }
        map.put("isPartOfRowsRegion", isPartOfRowsRegion);
        map.put("firstRow", firstRow);
        map.put("lastRow", lastRow);
        map.put("width", width);
        map.put("height", height);
        return map;
    }

    private static void createCell(CellStyle style, Row row, int columnIndex, String value){
        Cell cell = row.createCell(columnIndex);

        cell.setCellStyle(style);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(value);
    }

    private HashMap<Column, Integer> processTitleRow(QuerySession session, User user, Sheet sheet, Table table) {

        ArrayList columns = table.getModifiableColumns(Column.ADD);
        HashMap<String, Column> allModifiableColumns = new HashMap<>(64);
        HashMap<Column, Integer> result = new HashMap<>(64);
        for (int i = 0; i < columns.size(); i++) {
            Column column = (Column) columns.get(i);
            allModifiableColumns.put(column.getDescription(session.getLocale()), column);
            result.put(column, -1); //-1表示在excel中不存在对应列
        }

        int rows = sheet.getPhysicalNumberOfRows();
        if (rows <= 0) {
            throw new NDSException("导入的文件第一行必须为标题！");
        }
        Row titleRow = sheet.getRow(0);
        if (titleRow == null) {
            throw new NDSException("导入的文件第一行必须为标题！");
        }

        HashSet<String> existColumns = new HashSet<>();
        List<String> unWritableColumns = Permissions.getUnWritableColumns(user);
        for (int i = 0; i <= titleRow.getLastCellNum(); i++) {
            Cell cell = titleRow.getCell(i);
            if (cell == null
                    || cell.getCellType() != HSSFCell.CELL_TYPE_STRING) {
                continue;
            }
            String columnDisplayName = cell.getRichStringCellValue().getString();
            if (columnDisplayName == null || columnDisplayName.isEmpty()) {
                continue;
            }
            columnDisplayName = columnDisplayName.trim();
            if (existColumns.contains(columnDisplayName)) {
                continue;
            }
            existColumns.add(columnDisplayName);
            Column column = allModifiableColumns.get(columnDisplayName);
            if (column != null) {
                for (String unWritableColumn : unWritableColumns) {
                    if (unWritableColumn == null) {
                        continue;
                    }
                    unWritableColumn = unWritableColumn.trim();
                    if (unWritableColumn.isEmpty()) {
                        continue;
                    }
                    if (unWritableColumn.equalsIgnoreCase(column.getName())) {
                        throw new NDSException("您使用的导入模板已失效！请重新下载！");
                    }

                    DisplaySetting displaySetting = column.getDisplaySetting();
                    if (displaySetting != null && ("image".equalsIgnoreCase(displaySetting.getObjectTypeString())
                            || "file".equalsIgnoreCase(displaySetting.getObjectTypeString()))) {
                        log.debug(String.format("用户--%s生成表-%s的导入模板时，字段%s为image或者file列！", user.getId(), table.getId(), column.getName()));
                        continue;
                    }
                }
                result.put(column, i);
            }
        }
        return result;
    }

    private void recordError(SortedMap<Integer, JSONObject> errors, int rowIndex, String message){
        JSONObject currentError= errors.get(rowIndex);
        if(currentError == null){
            currentError = new JSONObject();
            currentError.put("rowIndex", rowIndex);
            currentError.put("message", message);
            errors.put(rowIndex, currentError);
        }else{
            String oldMessage = currentError.getString("message");
            currentError.put("message", oldMessage + "  " + message);
        }
    }

    private HashMap<Integer, JSONObject> processRows(SortedMap<Integer, JSONObject> errors, QuerySession session, Sheet sheet, Table table, HashMap<Column, Integer> columnMap) {
        HashMap<Integer, JSONObject> preProcessRows = Maps.newHashMap();
        HashMap<Integer, Table> needTranslateColumn = Maps.newHashMap();
        Multimap<Integer, String> needTranslateValues = HashMultimap.create();

        for (Column column : columnMap.keySet()) {
            Table refTable = column.getReferenceTable();
            if (refTable != null) {
                needTranslateColumn.put(column.getId(), refTable);
            }
        }

        //第一遍扫描出原始数据出来。
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            int currentRowIndex = i;
            Row currentRow = sheet.getRow(currentRowIndex);
            boolean isEmptyRow = true;
            if (currentRow == null) {
                continue;
            }
            JSONObject importRecord = new JSONObject();
            try {
                for (Column currentColumn : columnMap.keySet()) {
                    try {
                        int j = columnMap.get(currentColumn);
                        Cell cell;
                        if (j < 0) {
                            cell = null;
                        } else {
                            cell = currentRow.getCell(j, Row.CREATE_NULL_AS_BLANK);
                        }
                        String orignalValue = getCellOrignalValue(cell, currentColumn, session);
                        if (orignalValue != null && !orignalValue.isEmpty()) {
                            Table refTable = needTranslateColumn.get(currentColumn.getId());
                            if (refTable != null) {
                                needTranslateValues.put(currentColumn.getId(), orignalValue);
                            }
                        }
                        JSONObject cellWithOrignalValue = new JSONObject();
                        cellWithOrignalValue.put("orignalValue", orignalValue);
                        cellWithOrignalValue.put("cell", cell);
                        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK
                                && orignalValue != null && !orignalValue.trim().isEmpty()) {
                            isEmptyRow = false;
                        }
                        importRecord.put(currentColumn.getName(), cellWithOrignalValue);
                    }catch (Exception e) {
                        log.error(e.getMessage(), e);
                        recordError(errors, currentRowIndex, e.getMessage());
                    }
                }
                if (isEmptyRow) {
                    log.debug(String.format("行%s为空行，直接略过！", currentRowIndex));
                } else {
                    preProcessRows.put(currentRowIndex, importRecord);
                }
            } catch (Exception e) {
                log.debug(e.getMessage(), e);
                recordError(errors, currentRowIndex, e.getMessage());
            }
        }

        HashMap<Integer, HashMap<String, Long>> translatedValues = Maps.newHashMap();
        //调用外部服务获取AK对应的ID
        for (Integer columnId : needTranslateValues.keySet()) {
            Collection<String> tempObj = needTranslateValues.get(columnId);
            translatedValues.put(columnId, getIdFromAks(session, columnId, tempObj));
        }

        //第二次扫描，对值做各种转换,拼接出最后的结果来
        HashMap<Integer, JSONObject> result = Maps.newHashMap();
        for (Integer currentRowIndex : preProcessRows.keySet()) {
            try {
                JSONObject importRecord = preProcessRows.get(currentRowIndex);
                JSONObject newRecord = new JSONObject();
                for (String columnName : importRecord.keySet()) {
                    try {
                        JSONObject cellWithOrignalValue = importRecord.getJSONObject(columnName);
                        String orignalValue = cellWithOrignalValue.getString("orignalValue");
                        Cell cell = (Cell) cellWithOrignalValue.get("cell");
                        Column column = table.getColumn(columnName);
                        if (needTranslateColumn.containsKey(column.getId())
                                && orignalValue != null && !orignalValue.trim().isEmpty()) {
                            String[] tokens = StringUtils.split(orignalValue.trim(), ",");
                            List<Long> values = Lists.newArrayList();
                            for (String token : tokens) {
                                Long value = translatedValues.get(column.getId()).get(token);
                                if (value == null || value < 0) {
                                    throw new NDSException(String.format("%s: %s 不存在！", column.getDescription(session.getLocale()), token));
                                }
                                values.add(value);
                            }

                            newRecord.put(columnName, StringUtils.join(values.toArray(), ","));
                        } else {
                            Object value = getCellValue(cell, orignalValue, column, session);
                            if (value != null) {
                                newRecord.put(columnName, value);
                            }
                        }
                    }catch (Exception e) {
                        log.debug(e.getMessage(), e);
                        recordError(errors, currentRowIndex, e.getMessage());
                    }
                }
                if (newRecord.isEmpty()) {
                    log.debug("当前行为空行，不做新增动作！");
                    continue;
                }
                if(errors.containsKey(currentRowIndex)){
                    log.debug("当前行有错误，不做后续动作！");
                    continue;
                }

                //增加界面不显示字段默认值传入，识别第6位
                ArrayList hiddenColumns = table.getColumns(new int[]{6}, false, 0, true);
                hiddenColumns.forEach(item -> {
                    String val = ((Column) item).getDefaultValue();
                    val = QueryUtils.replaceVariables(val, session);
                    newRecord.put(((Column) item).getName(), normalize((Column) item, val));
                });
                result.put(currentRowIndex, newRecord);
            } catch (Exception e) {
                log.debug(e.getMessage(), e);
                recordError(errors, currentRowIndex, e.getMessage());
            }
        }
        return result;
    }

    private HSSFWorkbook getExcelWorkbook(MultipartFile file, String tableName, Table table) throws IOException {
        if (file == null || file.isEmpty()) {
            throw new NDSException("未选择导入文件！");
        }

        if (table == null) {
            throw new NDSException(String.format("表%s不存在", tableName));
        }

        try {
            InputStream stream = file.getInputStream();
            HSSFWorkbook workbook = new HSSFWorkbook(stream);
            if (workbook.getNumberOfSheets() <= 0) {
                throw new NDSException("导入的文件没有数据！");
            }
            return workbook;
        }catch (NDSException e){
            throw e;
        }
        catch (Exception e) {
            throw new NDSException("只支持XLS格式！");
        }
    }

    private Object getCellValue(Cell cell, String orignalValue, Column column, QuerySession session) {
//        if (orignalValue == null || orignalValue.isEmpty()) {
//            orignalValue = QueryUtils.replaceVariables(column.getDefaultValue(), session);
//            if (orignalValue == null || orignalValue.isEmpty()) {
//                if (!column.isNullable()) {
//                    throw new NDSException(String.format("%s:不可为空！", column.getDescription(session.getLocale())));
//                }
//            }
//            orignalValue = normalize(column, orignalValue);
//        }
        if (orignalValue == null || orignalValue.isEmpty()) {
            int displayType = column.getDisplaySetting().getObjectType();
            if (displayType == DisplaySetting.OBJ_CHECK) {
                //处理下默认值，TODO:如果可能的话需要在字段选项组里面加上默认值。
                orignalValue = QueryUtils.replaceVariables(column.getDefaultValue(), session);
            }
            if (orignalValue == null || orignalValue.isEmpty()) {
                if (!column.isNullable()) {
                    throw new NDSException(String.format("%s:不可为空！", column.getDescription(session.getLocale())));
                }
                return null;
            }
        }

        if (cell == null) {
            orignalValue = normalize(column, orignalValue); //照道理是不会进到这个分支来的。
        }
        try {
            switch (column.getType()) {
                case Column.NUMBER:
                    return Double.parseDouble(orignalValue);
                case Column.DATE:
                    return toDateTime(cell.getCellType(), orignalValue, FastDateFormat.getInstance("yyyy-MM-dd HH:mm:ss"));
                case Column.DATENUMBER:
                    return toDateTime(cell.getCellType(), orignalValue, QueryUtils.dateNumberFormatter);
                case Column.STRING:
                case Column.OTHER:
                    return normalize(column, orignalValue);
                default:
                    throw new NDSException(String.format("%s:字段类型错误！", column.getDescription(session.getLocale())));
            }
        } catch (Exception e) {
            throw new NDSException(String.format("%s：值%s 数据格式错误!", column.getDescription(session.getLocale()), orignalValue));
        }
    }

    private String normalize(Column column, String orignalValue) {
        //是否需要转化大小写
        if (orignalValue != null && column.isUpperCase()) {
            return orignalValue.toUpperCase();
        }
        return orignalValue;
    }

    private String toDateTime(int cellType, String orignalValue, FastDateFormat formatter) {
        if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
            double temp = Double.parseDouble(orignalValue);
            Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(temp);
            return formatter.format(date);
        } else {
            //直接认定为字符串形式
            Date date = DateUtil.stringToDate(orignalValue);
            return formatter.format(date);
        }
    }

    private String getCellOrignalValue(Cell cell, Column column, QuerySession session) {
        String value = null;
        int columnType = column.getType();
        if (cell == null) {
            value = ""; //等同于CELL_TYPE_BLANK
        } else {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING://字符串
                    value = cell.getRichStringCellValue().getString();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC://数字
//                    if (columnType == Column.STRING || columnType == Column.OTHER) {
//                        throw new NDSException(String.format("%s: 必须是文本格式！", column.getDescription(session.getLocale())));
//                    }
                    double temp = cell.getNumericCellValue();
                    if ((long) temp == temp) {
                        value = (long) temp + "";
                    } else {
                        value = temp + "";
                    }
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN://boolean型值
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    try {
                        value = String.valueOf(cell.getNumericCellValue());
                    } catch (Exception e) {
                        value = String.valueOf(cell.getRichStringCellValue());
                    }
                    break;
                default:
                    throw new NDSException(String.format("%s: 字段格式错误！", column.getDescription(session.getLocale())));
            }
        }

        //做下转义，用户上传的是value,转义成key来保存。
        int displayType = column.getDisplaySetting().getObjectType();
        if (displayType >= DisplaySetting.OBJ_SELECT && displayType <= DisplaySetting.OBJ_CHECK) {
            if (value != null && !value.isEmpty()) {
                Object temp = column.getValues(session.getLocale()).getKey(value);
                if (temp == null) {
                    throw new NDSException(String.format("%s: %s 不存在！", column.getDescription(session.getLocale()), value));
                }
                value = temp.toString();
            }
        }
        value = value.trim();
        if (value != null && value.isEmpty()) {
            value = null;
        }
        return value;
    }

    private Collection<String> distinct(Collection<String> values) {
        Set<String> result = Sets.newHashSet();
        for (String orignalValue : values) {
            if (orignalValue == null || orignalValue.trim().isEmpty()) {
                continue;
            }
            String[] tokens = StringUtils.split(orignalValue.trim(), ",");
            for (String token : tokens) {
                if (token == null || token.trim().isEmpty()) {
                    continue;
                }
                result.add(token);
            }
        }
        return result;
    }

    private HashMap<String, Long> getIdFromAks(QuerySession session, Integer columnId, Collection<String> values) {
        User user = session.getUser();
        TableManager tm = session.getTableManager();
        Column column = tm.getColumn(columnId);
        Table refTable = column.getReferenceTable();
        String center = refTable.getCategory().getSubSystem().getCenter();
        String[] gv = center.split(":");
        if (gv.length != 2) {
            throw new NDSException("center is error");
        }
        Object o = ReferenceUtil.refer(ApplicationContextHandle.getApplicationContext(), searchServiceName, gv[0], gv[1]);
        log.info("edas service->" + searchServiceName + ":" + gv[0] + ":" + gv[1]);

        Collection<String> distinctValues = distinct(values);
        JSONObject result = ((SearchCmd) o).queryIDByAKs(user, column.getId(), distinctValues);
        log.debug("edas service->" + result);
        int code = Tools.getInt(String.valueOf(result.get("code")), -1);
        if (code < 0) {
            String message = result.getString("message");
            throw new NDSException(String.format("字段 %s：试图调用queryIDByAKs接口失败！ %s", column.getDescription(user.getLocale()), distinctValues, message));
        }
        JSONArray data = result.getJSONArray("data");
        HashMap<String, Long> rowValues = Maps.newHashMap();
        for (String orignalValue : distinctValues) {
            rowValues.put(orignalValue, -1L);
        }

        if (data != null) {
            for (int i = 0; i < data.size(); i++) {
                JSONObject subTemp = data.getJSONObject(i);
                String ak = subTemp.getString("AK");
                long id = Tools.getLong(subTemp.getLong("ID"), -1L);
                if (ak != null && !ak.isEmpty() && rowValues.containsKey(ak)) {
                    rowValues.put(ak, id);
                }
            }
        }
        return rowValues;
    }

    /**
     * 下载导入模板
     *
     * @param request
     * @return
     * @throws Exception
     */
    @ApiOperation(value = "下载导入模板", notes = "生成导入excel模板")
    @RequestMapping(path = "/p/cs/downloadImportTemplate", method = RequestMethod.GET)
    public JSONObject downloadImpotTemplate(HttpServletRequest request,
                                            @RequestParam(value = "searchdata", required = true) String searchdata) throws Exception {
        //TODO:缓存起来，但失效的逻辑需要考虑（表和字段上配置转发处理程序（只捕获表的update动作、字段的insert/update/delete动作））
        User user = (User) ShiroSessionUtil.getAttribute("user");
//        User user = Security4Utils.getUser("root");
        QuerySession session = QueryUtils.createQuerySession(user);
        TableManager tm = session.getTableManager();

        JSONObject jo = JSON.parseObject(searchdata);
        Table table = null;
        int tableid = jo.getIntValue("tableid");

        if (tableid > 0 && table == null) {
            table = tm.getTable(tableid);
        }
        if (jo.getString("table") != null && table == null) {
            table = tm.getTable(jo.getString("table"));
        }
        if (table == null) {
            throw new NDSException("tablenull");
        }

        String location = ResourceUtils.getURL("/home/admin/tmp").getPath();
        location = location + "/import" + File.separator + user.getId() + File.separator;

        File svrDir = new File(location);
        if (!svrDir.isDirectory()) {
            svrDir.mkdirs();
        }
        String fullFileName = location + table.getDescription() + ".xls";
        String path = File.separator + "import" + File.separator + user.getId() + File.separator + table.getDescription() + ".xls";
        try {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet(table.getDescription());

            sheet.createFreezePane(0, 1, 0, 1);
            ArrayList columns = table.getModifiableColumns(Column.ADD); // table.getColumns(new int[]{Column.ADD}, false,-1);
            Cell cell;

            List<String> unWritableColumns = Permissions.getUnWritableColumns(user);
            ArrayList tempColumns = Lists.newArrayList();

            for (int i = 0; i < columns.size(); i++) {
                Column col = (Column) columns.get(i);
                String columnName = col.getName();
                boolean isWritable = true;
                for (String unWritableColumn : unWritableColumns) {
                    if (unWritableColumn == null) {
                        continue;
                    }
                    unWritableColumn = unWritableColumn.trim();
                    if (unWritableColumn.isEmpty()) {
                        continue;
                    }
                    if (unWritableColumn.equalsIgnoreCase(columnName)) {
                        isWritable = false;
                        break;
                    }
                }
                if (!isWritable) {
                    log.debug(String.format("用户--%s生成表-%s的导入模板时，字段%s为敏感列！", user.getId(), table.getId(), columnName));
                } else {
                    tempColumns.add(col);
                }
            }
            columns = tempColumns;

            tempColumns = Lists.newArrayList();
            for (int i = 0; i < columns.size(); i++) {
                Column col = (Column) columns.get(i);
                String columnName = col.getName();
                DisplaySetting displaySetting = col.getDisplaySetting();
                if (displaySetting != null && ("image".equalsIgnoreCase(displaySetting.getObjectTypeString())
                        || "file".equalsIgnoreCase(displaySetting.getObjectTypeString()))) {
                    log.debug(String.format("用户--%s生成表-%s的导入模板时，字段%s为image或者file列！", user.getId(), table.getId(), columnName));
                } else {
                    tempColumns.add(col);
                }
            }
            columns = tempColumns;

            Row titleRow = sheet.createRow(0);
            for (int i = 0; i < columns.size(); i++) {
                Column col = (Column) columns.get(i);
                cell = titleRow.createCell((short) i);
                cell.setCellStyle(getStyleByColumn(wb, sheet, col, i, session));
                String expression = getExpression(columns, session, col);
                if (expression != null && !expression.isEmpty()) {
                    try {
                        setFormula(sheet, cell, i, expression);
                    } catch (Exception e) {
                        log.error("公式设置异常:" + path, e);
                    }
                } else {
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                }
                sheet.setDefaultColumnStyle(i, getDefaultStyle(wb, col));
                String colDisplayName = col.getDescription(session.getLocale());
                cell.setCellValue(colDisplayName);
                sheet.setColumnWidth((short) i, (colDisplayName.getBytes().length * 256 * 2));
            }
            FileOutputStream fileOut = new FileOutputStream(fullFileName);
            wb.write(fileOut);
            fileOut.close();
        } catch (Exception e) {
            log.error("导入模板生成失败:" + path, e);
            throw new NDSException(e.getMessage(), e);
        }

        ValueHolder valueHolder = new ValueHolder();
        valueHolder.put("code", 0);
        valueHolder.put("data", path);
        return valueHolder.toJSONObject();
    }


    private void setFormula(Sheet sheet, Cell cell, int columnIndex, String expExecContent) {
        //cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        CellRangeAddress region = new CellRangeAddress(1, 1, columnIndex, columnIndex);
        sheet.setArrayFormula(expExecContent, region);
        sheet.setForceFormulaRecalculation(true);
    }

    private String getExpression(ArrayList columns, QuerySession session, Column currentColumn) {
        String expression = null;
        try {
            JSONObject jsonObject = currentColumn.getJSONProps();
            if (jsonObject != null) {
                expression = jsonObject.getString("importExp");
                if (expression != null) {
                    expression = calculateExpression(session, columns, expression);
                }
            }
        } catch (Exception e) {
            String message = ExceptionUtil.getMessage(e);
            log.error(message);
            return null;
        }
        return expression;
    }

    private String calculateExpression(QuerySession session, ArrayList columns, String expression) {
        String regex = "#{1}(.*?)#{1}";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(expression);
        while (matcher.find()) {
            String columnName = matcher.group(1);
            if (columnName == null) {
                continue;
            }
            columnName = columnName.trim();
            if (columnName.isEmpty()) {
                continue;
            }
            boolean isExisColumn = false;

            for (int j = 0; j < columns.size(); j++) {
                String replaceChar = getHiddenColumnName(j) + "2";
                Column col1 = (Column) columns.get(j);
                String temp = col1.getName();
                if ((" " + columnName + " ").indexOf(temp) > -1) {
                    String execContent = "#" + columnName + "#";
                    columnName = columnName.replace(temp, replaceChar);
                    expression = expression.replace(execContent, columnName);
                    isExisColumn = true;
                    break;
                }

            }
            if (!isExisColumn) {
                log.debug("生成导入模板,公式%s不起效！字段%s不是模板列！", expression, columnName);
                return null;
            }
        }
        return expression;
    }


    private CellStyle getDefaultStyle(Workbook wb, Column column) {
        Font font = wb.createFont();
        font.setFontHeightInPoints((short) 10);
        font.setFontName("宋体");
        font.setColor(HSSFColor.DARK_BLUE.index);
        CellStyle style = wb.createCellStyle();
        style.setFont(font);

        int columnType = column.getType();
        DataFormat format = wb.createDataFormat();
        CreationHelper creationHelper = wb.getCreationHelper();
        int displayType = column.getDisplaySetting().getObjectType();
        if (displayType >= DisplaySetting.OBJ_SELECT && displayType <= DisplaySetting.OBJ_CHECK) {
            style.setDataFormat(format.getFormat("text"));
        } else {
            Table refTable = column.getReferenceTable();
            if (refTable != null) {
                style.setDataFormat(format.getFormat("text"));
            } else {
                switch (columnType) {
                    case Column.STRING:
                    case Column.OTHER:
                        style.setDataFormat(format.getFormat("text"));
                        break;
//            case Column.NUMBER:
//                style.setDataFormat(format.getFormat("#,##0.00"));
//                break;
                    case Column.DATE:
                        style.setDataFormat(creationHelper.createDataFormat().getFormat("yyyyMMdd  hh:mm:ss"));
                        break;
                    case Column.DATENUMBER:
                        style.setDataFormat(creationHelper.createDataFormat().getFormat("yyyyMMdd"));
                        break;
                    default:
                        style.setDataFormat(format.getFormat("General"));
                }
            }
        }

        return style;
    }

    private CellStyle getStyleByColumn(Workbook wb, Sheet sheet, Column column, int columnIndex, QuerySession session) throws Exception {
        Font font = wb.createFont();
        font.setFontHeightInPoints((short) 10);
        font.setFontName("宋体");

        font.setColor(HSSFColor.DARK_BLUE.index);
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);

        if (!column.isNullable()) {
            font.setColor(HSSFColor.RED.index);
        }
        // Fonts are set into a style so create a new one to use.
        CellStyle style = wb.createCellStyle();
        style.setFont(font);

        CellRangeAddressList regions = new CellRangeAddressList(0, EXCEL_MAX_ROW_COUNT, columnIndex, columnIndex);
        int displayType = column.getDisplaySetting().getObjectType();
        if (displayType >= DisplaySetting.OBJ_SELECT && displayType <= DisplaySetting.OBJ_CHECK) {
            List<String> keys = column.getValues(session.getLocale()).valueList();
            DVConstraint constraint = createConstraint(wb, column.getName(), columnIndex, keys); //DVConstraint.createExplicitListConstraint(keys.toArray(new String[0]));
            if (constraint != null) {
                HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
                sheet.addValidationData(dataValidate);
            }

//            //对应的提示
//            constraint = DVConstraint.createCustomFormulaConstraint("BB1");
//            HSSFDataValidation data_validation_view = new HSSFDataValidation(regions,constraint);
//            data_validation_view.createPromptBox("可选值：", column.getValues(session.getLocale()).toString());
//            sheet.addValidationData(data_validation_view);
        } else {
            Table refTable = column.getReferenceTable();
            if (refTable != null) {
                String fkDisplay = column.getFkdisplay();
                if (!"mrp".equalsIgnoreCase(fkDisplay) && !"mop".equalsIgnoreCase(fkDisplay)) {
                    JSONObject jsonProperties = refTable.getJSONProps();
                    if (jsonProperties != null) {
                        Boolean isSmallTable = jsonProperties.getBoolean("smalltable");
                        //小表加约束
                        if (isSmallTable != null && isSmallTable) {
                            List<String> keys = dumpSmallTableAks(session.getUser(), refTable, column);
                            DVConstraint constraint = createConstraint(wb, column.getName(), columnIndex, keys); // DVConstraint.createExplicitListConstraint(keys.toArray(new String[0]));
                            if (constraint != null) {
                                HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
                                sheet.addValidationData(dataValidate);
                            }
                        }
                    }
                }
            }
        }

        style.setLocked(true);
        return style;
    }

    private DVConstraint createConstraint(Workbook workbook, String inputColumnName, int columnIndex, List<String> values) {
        if (values == null || values.size() <= 0) {
            return null;
        }
        Sheet hidden = workbook.getSheet("hidden");
        if (hidden == null) {
            hidden = workbook.createSheet("hidden");
            workbook.setSheetHidden(1, true);
            hidden.protectSheet("hidden");
        }
        for (int i = 0, length = values.size(); i < length; i++) {
            String value = values.get(i);
            Row row = hidden.getRow(i);
            if (row == null) {
                row = hidden.createRow(i);
            }
            Cell cell = row.createCell(columnIndex);
            cell.setCellValue(value);
        }

        Name namedCell = workbook.createName();
        namedCell.setNameName("hidden" + inputColumnName);
        String columnName = getHiddenColumnName(columnIndex);
        namedCell.setRefersToFormula("hidden" + "!$" + columnName + "$1:$" + columnName + "$" + values.size());
        //加载数据,将名称为hidden的
        DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden" + inputColumnName);
        return constraint;
    }


    private String template = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

    private String getHiddenColumnName(int columnIndex) {
        int modValue = columnIndex % 26;
        int leftValue = (columnIndex - modValue) / 26;
        Character result = template.charAt(modValue);

        if (leftValue > 0) {
            return getHiddenColumnName(leftValue - 1) + result;
        }
        return result.toString();
    }

    private List<String> dumpSmallTableAks(User user, Table refTable, Column column) throws Exception {
        String center = refTable.getCategory().getSubSystem().getCenter();
        String[] gv = center.split(":");
        if (gv.length != 2) {
            throw new NDSException("center is error");
        }
        Object o = ReferenceUtil.refer(ApplicationContextHandle.getApplicationContext(), importServiceName, gv[0], gv[1]);
        log.info("edas service->" + importServiceName + ":" + gv[0] + ":" + gv[1]);

        JSONObject result = ((ImportCmd) o).dumpSmallTable(user, column.getId());
        int code = Tools.getInt(String.valueOf(result.get("code")), 0);
        if (code < 0) {
            String message = result.getString("message");
            throw new NDSException(String.format("字段 %s：小表导出错误！%s", column.getDescription(user.getLocale()), message));
        }
        JSONArray data = result.getJSONArray("data");
        if (data == null) {
            return Collections.emptyList();
        }
        List<String> coll = new ArrayList<>();
        for (int i = 0; i < data.size(); i++) {
            String temp = data.getString(i);
            if (temp != null && !temp.isEmpty()) {
                coll.add(temp);
            }
        }
        return coll;
    }
}
